﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using ECOSYSTEM_Dominio;

namespace ECOSYSTEM_DAL
{
    public class SubLineaDAL
    {
        SqlConnection cnn;
        Int32 SiHay;
        public Boolean guardarSubLinea(SubLinea laSubLinea) { 
            try
            {
                cnn = (SqlConnection)ConexionSQL.getInstancia().getConexion();
                cnn.Open();
                SqlCommand cmd = new SqlCommand("RegistrarSubLinea",cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@SubLineaID", SqlDbType.Int,6);
                cmd.Parameters["@SubLineaID"].Direction = ParameterDirection.InputOutput;
                cmd.Parameters.Add("@NomSubLinea", SqlDbType.NVarChar,100);
                cmd.Parameters.Add("@DescSubLinea", SqlDbType.Text);
                cmd.Parameters["@SubLineaID"].Value = laSubLinea.SubLineaID;
                cmd.Parameters["@NomSubLinea"].Value = laSubLinea.NomSubLinea;
                cmd.Parameters["@DescSubLinea"].Value = laSubLinea.DescSubLinea;
                cmd.ExecuteNonQuery();
                laSubLinea.SubLineaID =(Int32)(cmd.Parameters["@SubLineaID"].Value);
            }
            catch (SqlException ex) { throw new Exception(ex.Message); }
            catch (Exception e) { throw new Exception(e.Message); }
            finally
            {
                if (cnn.State == ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
            return true;
        }
        public DataTable ObtenerSubLinea() {
            DataTable lista = null;
            try
            {
                cnn = (SqlConnection)ConexionSQL.getInstancia().getConexion();
                cnn.Open();
                SqlDataAdapter da = new SqlDataAdapter("ObtenerSubLinea", cnn);
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                lista = new DataTable();
                da.Fill(lista);
            }
            catch (SqlException ex) { throw new Exception(ex.Message); }
            catch (Exception e) { throw new Exception(e.Message); }
            finally
            {
                if (cnn.State == ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
            return lista;
        }
        public List<SubLinea> BuscarSubLineaNombre(String nomSubLinea) { 
             List<SubLinea> lista;
            try
            {
                cnn = (SqlConnection)ConexionSQL.getInstancia().getConexion();
                cnn.Open();
                SqlCommand cmd = new SqlCommand("BuscarSubLineaNombre", cnn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@NomSubLinea", SqlDbType.NVarChar,100);
                cmd.Parameters["@NomSubLinea"].Value = nomSubLinea;
                lista = new List<SubLinea>();
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read()) {
                    SubLinea laSubLinea = new SubLinea();
                    laSubLinea.SubLineaID = Convert.ToInt32(dr["SubLineaID"]);
                    laSubLinea.NomSubLinea = dr["NomSubLinea"].ToString();
                    laSubLinea.DescSubLinea = dr["DescSubLinea"].ToString();
                    lista.Add(laSubLinea);
                }
                return lista;
            }
            catch (SqlException e) { throw new Exception(e.Message); }
            catch (Exception ex) { throw new Exception(ex.Message); }
            finally
            {
                if (cnn.State == ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
        }
        public Boolean ActualizarSubLinea(SubLinea laSubLinea)
        {
            try
            {
                cnn = (SqlConnection)ConexionSQL.getInstancia().getConexion();
                cnn.Open();
                SqlCommand cmd = new SqlCommand("ActualizarSubLinea", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@SubLineaID", SqlDbType.Int, 6);
                cmd.Parameters.Add("@NomSubLinea", SqlDbType.NVarChar, 100);
                cmd.Parameters.Add("@DescSubLinea", SqlDbType.Text);
                cmd.Parameters["@SubLineaID"].Value = laSubLinea.SubLineaID;
                cmd.Parameters["@NomSubLinea"].Value = laSubLinea.NomSubLinea;
                cmd.Parameters["@DescSubLinea"].Value = laSubLinea.DescSubLinea;
                cmd.ExecuteNonQuery();
            }
            catch (SqlException ex) { throw new Exception(ex.Message); }
            catch (Exception e) { throw new Exception(e.Message); }
            finally
            {
                if (cnn.State == ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
            return true;
        }
        public Int32 NuevaSubLineaCod(String nombreSubLinea, String DescripcionSubLinea)
        {
            try
            {
                cnn = (SqlConnection)ConexionSQL.getInstancia().getConexion();
                cnn.Open();
                SqlCommand cmd = new SqlCommand("ObtenerUltimoCodigoSubLinea", cnn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@NomSubLinea", SqlDbType.NVarChar, 100);
                cmd.Parameters.Add("@DescSubLinea", SqlDbType.Text);
                cmd.Parameters["@NomSubLinea"].Value = nombreSubLinea;
                cmd.Parameters["@DescSubLinea"].Value = DescripcionSubLinea;
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    SiHay = Convert.ToInt32(dr["NewCod"]);
                }
            }
            catch (SqlException e) { throw new Exception(e.Message); }
            catch (Exception ex) { throw new Exception(ex.Message); }
            finally
            {
                if (cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
            return SiHay;
        }
    }
}
